import json
import xlrd,xlwt
from xlutils.copy import copy

with open("score.json","r",encoding='utf-8') as readdata:
    detail = json.load(readdata)
print(detail)
workbook = xlwt.Workbook()
sheet1 =workbook.add_sheet("student")
title =['学号','姓名','语文成绩','数学成绩','英语成绩','总分','平均分']
for i in range(len(title)):
#    print(title[i])
sheet1.write(0,i,title[i])
dictkey = list(detail.keys())
dictvalue =list(detail.values())
#print(dictkey)
#print(dictvalue)
row =1
for j in dictkey:
#    print(j)
sheet1.write(row,0,j)
row =row +1

i =0
a =1
for j in range(len(dictvalue)):
b = 1
num = 0
for x in dictvalue[i]:
print(x)
sheet1.write(a,b,x)
b=b+1
if isinstance(x,int) or isinstance(x,float):
num += x
print(num)
sheet1.write(a,5,num)
sheet1.write(a,6,round(num/3,2))
a =a+1
i = i+1

workbook.save("student.xlsx")
#老师突然发现，每个学生的数学成绩都计算错了，都多加了4分，那么需要修改excel将每个同学数学成绩都减4分，请用程序实现。
exceldata = xlrd.open_workbook("student.xlsx",formatting_info=True)
newtable=copy(exceldata)
sheet=newtable.get_sheet(0)
#读取文件原有内容
sheet2 = exceldata.sheet_by_index(0)
cols = sheet2.col_values(3)
print(cols)
z=0
for y in cols:
    print(y)
    if isinstance(y,int) or isinstance(y,float):
        sheet.write(z,3,y-4)
    z =z+1
newtable.save("student.xlsx")